package com.jk.libsm.mapper;

import com.jk.libsm.pojo.Book;
import com.jk.libsm.pojo.Borrowhistory;
import com.jk.libsm.vo.Bookhistory;
import com.jk.libsm.vo.Books;
import com.jk.libsm.vo.Booktop;
import org.apache.ibatis.annotations.*;
import com.jk.libsm.pojo.Borrowandreturn;
import java.util.ArrayList;

/**
 * @author lk
 * @date 2020/7/6 0006 23:16
 * @description:
 */
@Mapper
public interface BookMapper {

    // 按书名查询图书
    @Select("select DISTINCT BName,Bauthor,Bpublic,BKName,position,BNo from book,bookkind where BName like concat('%',#{BName},'%') and isborrow=#{flag} and book.BKNo=bookkind.BKNo and isappoinment=1 and isborrow=1")
    ArrayList<Books> getAllBooksByName(@Param("BName") String BName,@Param("flag") int flag);

    // 根据种类查询图书
    @Select("select  DISTINCT BName,Bauthor,Bpublic,BKName,position,BNo from book,bookkind where BKName like concat('%',#{BKName},'%') and isborrow=#{flag} and book.BKNo=bookkind.BKNo and isappoinment=1 and isborrow=1")
    ArrayList<Books> getAllBooksByKind(@Param("BKName") String BKName,@Param("flag") int flag);

    // 根据出版社查询图书
    @Select("select  DISTINCT BName,Bauthor,Bpublic,BKName,position,BNo from book,bookkind where Bpublic like concat('%',#{Bpublic},'%') and isborrow=#{flag} and book.BKNo=bookkind.BKNo and isappoinment=1 and isborrow=1")
    ArrayList<Books> getAllBooksByPress(@Param("Bpublic") String Bpublic,@Param("flag") int flag);

    // 根据作者查询图书
    @Select("select DISTINCT  BName,Bauthor,Bpublic,BKName,position,BNo from book,bookkind where Bauthor like concat('%',#{Bauthor},'%') and isborrow=#{flag} and book.BKNo=bookkind.BKNo and isappoinment=1 and isborrow=1")
    ArrayList<Books> getAllBooksByAuthor(@Param("Bauthor") String Bauthor,@Param("flag") int flag);

    // 根据位置查询图书
    @Select("select DISTINCT  BName,Bauthor,Bpublic,BKName,position,BNo from book,bookkind where position like concat('%',#{position},'%') and isborrow=#{flag} and book.BKNo=bookkind.BKNo and isappoinment=1 and isborrow=1")
    ArrayList<Books> getAllBooksByPosition(@Param("position") String position,@Param("flag") int flag);

    //查询被借次数最多的十本书
    @Select("select BNo,BName,Bauthor,BKName,Bpublic,borrowCount from book,bookkind  where book.BKNo=bookkind.BKNo order by borrowCount desc limit 10 ")
    ArrayList<Booktop> getBooktops();

    //查询所有借出去的书
    @Select("SELECT book.BNo,BName,Bauthor,BKName,Bpublic, RNo ,shouldreturndate from book,bookkind,borrowandreturn where isborrow=0 and book.BKNo=bookkind.BKNo and book.BNo=borrowandreturn.BNo")
    ArrayList<Booktop> getBookBorrow();

    //根据位置查询图书的分类
    @Select("select BKNo from bookkind where position = #{position}")
    int getBKNoByPosition(String position);

    // 插入一本书
    @Insert("insert into book (BName,BKNo,Bpublic,Bprice,Bauthor,Bintro,isappoinment,isborrow,borrowCount) values(#{BName},#{BKNo},#{Bpublic},#{Bprice},#{Bauthor},#{Bintro},#{isappoinment},#{isborrow},#{borrowCount})")
    void saveBook(Book book);

    // 查阅读者正在借阅的图书信息中的借书日期
    @Select("select borrowdate from borrowandreturn where RNo=#{RNo} and BNo=#{BNo}")
    String getBorrowDate(@Param("RNo") String RNo,@Param("BNo") long BNo);

    // 删除当前读者的借阅记录
    @Delete("delete from borrowandreturn where RNo=#{RNo} and BNo=#{BNo}")
    void deleteReaderBorrow(@Param("RNo") String RNo,@Param("BNo") long BNo);

    // 增加借阅历史
    @Insert("insert into borrowhistory(BNo,RNo,borrowdate,returndate) values(#{BNo},#{RNo},#{borrowdate},#{returndate})")
    void saveBorrowHistory(Borrowhistory borrowhistory);

    // 修改图书借阅状态
    @Update("update book set isappoinment=#{isappoinment},isborrow=#{isborrow} where BNo=#{BNo}")
    void updateBookBorrowState(@Param("isappoinment") int isappointment,@Param("isborrow") int isborrow,@Param("BNo") long BNo);

    // 查询用户超期未归还的信息
    @Select("select * from borrowandreturn where shouldreturndate < #{shouldreturndate}")
    ArrayList<Borrowandreturn> getBorrowandreturn(String shouldreturndate);

    //按照书号查询图书
    @Select("select * from book where BNo=#{BNo}")
    Book getBookByBNo(long BNo);

    //根据图书编号索引图书分类
    @Select("select BKName from book,bookkind where book.BNo=#{BNo} and book.BKNo=bookkind.BKNo")
    String getBKNmameByBNo(long BNo);
}
